<?php

require_once('../domain/User.php');

function get_user_by_username($nickname) {
    global $db;
    $query = 'SELECT * 
              FROM user
              WHERE nickname = :nickname';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':nickname', $nickname);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        return $result;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function get_user_by_initial_value($initial_value) {
    global $db;
    $query = 'SELECT * 
              FROM user
              WHERE initial_value = :initial_value';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':initial_value', $initial_value);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        $users = genDBO($result);
        return $users;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function get_best_users($from, $to) {
    global $db;
    $query = 'select nickname, sum(gefahrene_kilometer) as gefahren from user u
                join routen_eintrag r on (u.id = r.user_fk)
                group by nickname
                order by gefahren desc
                LIMIT :from , :to';

    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':from', $from, PDO::PARAM_INT);
        $statement->bindValue(':to', $to, PDO::PARAM_INT);
        $statement->execute();
        $results = $statement->fetchAll();
        $statement->closeCursor();
        $resultDTO = getBestDTO($results);
        return $resultDTO;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function findUserByNicknameAndEmail($nickname, $email) {
    global $db;
    $query = 'SELECT * 
              FROM user
              WHERE nickname = :nickname AND email = :email';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':nickname', $nickname);
        $statement->bindValue(':email', $email);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        $users = genDBO($result);
        return $users;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function getBestDTO($results) {
    $datas = array();
    $users = array();
    $count = 0;
    foreach ($results as $row) {
        $users[$count] = $row['nickname'];
        $datas[$count] = $row['gefahren'];
        $count++;
    }

    $bestResult = array();
    $bestResult['datas'] = $datas;
    $bestResult['users'] = $users;

    return $bestResult;
}

function get_user($nickname, $password) {
    global $db;
    $query = 'SELECT * 
              FROM user
              WHERE nickname = :nickname and password = :password';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':nickname', $nickname);
        $statement->bindValue(':password', $password);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        $users = genDBO($result);
        return $users;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function insert_user($alterskategorie, $nickname, $ort, $strasse, $initial_value, $geschlecht, $nachname, $password, $vorname, $plz, $email, $newsletter) {
    global $db;

    $query = 'INSERT INTO user
                 (alterskategorie, nickname, ort, strasse, geschlecht, 
                  initial_value, nachname, password, vorname, plz, email, newsletter)
              VALUES
                 (:alterskategorie, :nickname, :ort, :strasse, :geschlecht,
                  :initial_value, :nachname, :password, :vorname, :plz, :email, :newsletter)';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':alterskategorie', $alterskategorie);
        $statement->bindValue(':nickname', $nickname);
        $statement->bindValue(':ort', $ort);
        $statement->bindValue(':strasse', $strasse);
        $statement->bindValue(':geschlecht', $geschlecht);
        $statement->bindValue(':initial_value', $initial_value);
        $statement->bindValue(':nachname', $nachname);
        $statement->bindValue(':password', $password);
        $statement->bindValue(':vorname', $vorname);
        $statement->bindValue(':plz', $plz);
        $statement->bindValue(':email', $email);
        $statement->bindValue(':newsletter', $newsletter, PDO::PARAM_BOOL);
        $statement->execute();
        $statement->closeCursor();

        // Get the last product ID that was automatically generated
        $user_id = $db->lastInsertId();
        return $user_id;
    } catch (PDOException $e) {
        $error_message = 'Bei der Speicherung der Daten gab es einen Fehler.';
        /* for debug purpose */
        //$error_message = $e->getMessage();

        display_db_error($error_message);
    }
}

function isNicknameUnique($nickname) {
    global $db;
    $query = 'SELECT * 
              FROM user
              WHERE nickname = :nickname';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':nickname', $nickname);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        $users = genDBO($result);
        if (count($users) >= 1) {
            return false;
        } else {
            return true;
        }
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function isEmailUnique($email) {
    global $db;
    $query = 'SELECT * 
              FROM user
              WHERE email = :email';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':email', $email);
        $statement->execute();
        $result = $statement->fetchAll();
        $statement->closeCursor();
        $users = genDBO($result);
        if (count($users) >= 1) {
            return false;
        } else {
            return true;
        }
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function update_user_confirmed($user) {
    global $db;
    $query = 'UPDATE user
              SET isConfirmed = TRUE
              WHERE id = :id';
    $statement = $db->prepare($query);
    $statement->bindValue(':id', $user->getId());
    $row_count = $statement->execute();
    $statement->closeCursor();
    return $row_count;
}

function update_user_with_pw($user, $alter, $nickname, $ort, $strasse, $geschlecht, $nachname, $password, $vorname, $plz, $email, $newsletter) {
    global $db;
    $query = 'UPDATE user
              SET password = :password,
                  vorname = :vorname,
                  nachname = :nachname,
                  plz = :plz,
                  ort = :ort,
                  geschlecht = :geschlecht,
                  strasse = :strasse,
                  alterskategorie = :alter,
                  email = :email,
                  nickname = :nickname,
                  newsletter = :newsletter
              WHERE id = :id';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':id', $user->getId(), PDO::PARAM_INT);
        $statement->bindValue(':password', $password);
        $statement->bindValue(':vorname', $vorname);
        $statement->bindValue(':nachname', $nachname);
        $statement->bindValue(':plz', $plz);
        $statement->bindValue(':ort', $ort);
        $statement->bindValue(':geschlecht', $geschlecht);
        $statement->bindValue(':strasse', $strasse);
        $statement->bindValue(':alter', $alter);
        $statement->bindValue(':email', $email);
        $statement->bindValue(':nickname', $nickname);
        $statement->bindValue(':newsletter', $newsletter, PDO::PARAM_BOOL);
$row_count = $statement->execute();
        $statement->closeCursor();
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
    return $row_count;
}

function update_user($user, $alter, $nickname, $ort, $strasse, $geschlecht, $nachname, $vorname, $plz, $email, $newsletter) {
    global $db;
    $query = 'UPDATE user
              SET vorname = :vorname,
                  nachname = :nachname,
                  plz = :plz,
                  ort = :ort,
                  geschlecht = :geschlecht,
                  strasse = :strasse,
                  alterskategorie = :alter,
                  email = :email,
                  nickname = :nickname,
                  newsletter = :newsletter
              WHERE id = :id';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':id', $user->getId(), PDO::PARAM_INT);
        $statement->bindValue(':vorname', $vorname);
        $statement->bindValue(':nachname', $nachname);
        $statement->bindValue(':plz', $plz);
        $statement->bindValue(':ort', $ort);
        $statement->bindValue(':geschlecht', $geschlecht);
        $statement->bindValue(':strasse', $strasse);
        $statement->bindValue(':alter', $alter);
        $statement->bindValue(':email', $email);
        $statement->bindValue(':nickname', $nickname);
        $statement->bindValue(':newsletter', $newsletter, PDO::PARAM_BOOL);
        $row_count = $statement->execute();
        $statement->closeCursor();
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
    return $row_count;
}

function update_user_password($user, $newPassword) {
    global $db;
    $query = 'UPDATE user
              SET password = :password
              WHERE id = :id';
    $statement = $db->prepare($query);
    $statement->bindValue(':id', $user->getId());
    $statement->bindValue(':password', $newPassword);
    $row_count = $statement->execute();
    $statement->closeCursor();
    return $row_count;
}

function delete_product($product_id) {
    global $db;
    $query = 'DELETE FROM products WHERE productID = :product_id';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':product_id', $product_id);
        $row_count = $statement->execute();
        $statement->closeCursor();
        return $row_count;
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}

function genDBO($result) {
    $users = array();
    foreach ($result as $row) {
        $user = new User($row['alterskategorie'],
                        $row['id'],
                        $row['nickname'],
                        $row['ort'],
                        $row['strasse'],
                        $row['geschlecht'],
                        $row['initial_value'],
                        $row['isConfirmed'],
                        $row['nachname'],
                        $row['password'],
                        $row['vorname'],
                        $row['plz'],
                        $row['email'],
                        $row['newsletter']);
        $users[] = $user;
    }
    return $users;
}

?>